package org.bigdata.framework.core.protocol;

import java.math.BigDecimal;
import java.net.InetAddress;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bigdata.framework.core.model.Classes;
import org.bigdata.framework.core.model.DataHandling;
import org.bigdata.framework.core.model.DataSource;
import org.bigdata.framework.core.model.Member;
import org.bigdata.framework.core.model.ShardingDataSource;
import org.bigdata.framework.core.model.ShardingRepositoryCount;
import org.bigdata.framework.core.model.ShardingRepositoryDataSource;
import org.bigdata.framework.core.service.IDataBackupService;
import org.bigdata.framework.core.service.IDataRecoveryService;
import org.bigdata.framework.core.utils.ConstantSql;
import org.bigdata.framework.core.utils.CreateSecrteKey;
import org.bigdata.framework.core.utils.DateUtils;
import org.bigdata.framework.core.utils.DbConnection;
import org.bigdata.framework.core.utils.FileUtils;
import org.bigdata.framework.core.utils.IdWorker;
import org.bigdata.framework.core.utils.JdbcUtils;
import org.bigdata.framework.core.utils.MD5;
import org.bigdata.framework.core.utils.MybatisPlusTools;
import org.bigdata.framework.core.utils.ReadXml;
import org.bigdata.framework.core.utils.TxtUtil;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;

import com.baomidou.mybatisplus.plugins.Page;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.MongoIterable;
import com.xxl.conf.core.XxlConfClient;


public class DataHandlingService implements IDataHandlingService{

	@Autowired
	private org.bigdata.framework.core.service.IDataHandlingService dataHandlingService;
	@Autowired
	private IBaseService baseService;
	@Autowired
	private IPublicService publicService;
	@Autowired
	private IDynamicService dynamicService;
	@Autowired
	private IDataBackupService dataBackupService;
	@Autowired
	private IDataRecoveryService dataRecoveryService;
	
	IdWorker aIdWorker = new IdWorker(2);
	
	@Override
	public List<DataHandling> getDataHandling(HashMap<String, Object> queryParams, int pageNumber, int pageSize) {
		return dataHandlingService.selectPage(new Page<DataHandling>(pageNumber, pageSize), new MybatisPlusTools<DataHandling>().getEwByList(queryParams)).getRecords();
	}

	@Override
	public int getDataHandlingCount(HashMap<String, Object> queryParams) {
		return dataHandlingService.selectCount(new MybatisPlusTools<DataHandling>().getEwByCount(queryParams));
	}

	@Override
	public DataHandling findDataHandling(Integer id) {
		return dataHandlingService.selectById(id);
	}

	@Override
	public List<DataHandling> getDataHandling() {
		return dataHandlingService.selectList(null);
	}

	@Override
	public boolean getConnectionTest(String url, String user, String password,
			String coding, String type) {
		boolean flag = false;
		Connection conn = null;
		MongoDatabase mongoDatabase=null;
		if(type.equals("mongodb")){
			mongoDatabase = getConnection(url, user, password);
			MongoIterable<String> listCollectionNames = mongoDatabase.listCollectionNames();
			for (String s : listCollectionNames) {
				flag=true;
			}
			return flag;
		}else{
			conn = getConnection(url, user, password, coding, type);
			if(conn==null){
				flag=false;
			}else{
				flag=true;
			}
			JdbcUtils.close(conn);
		}
		
		return flag;
	}

	@Override
	public DataHandling saveDataHandling(DataHandling dataHandling) {
		boolean r = dataHandlingService.insert(dataHandling);
		if(r){
			return dataHandling;
		}
		return null;
	}

	@Override
	public DataHandling editDataHandling(DataHandling dataHandling) {
		boolean u = dataHandlingService.updateById(dataHandling);
		if(u){
			return dataHandling;
		}
		return null;
	}

	@Override
	public void deleteDataHandling(DataHandling dataHandling) {
		//sqlManager.deleteById(DataHandlingImport.class, dataHandlingImport.getId());
		boolean d = dataHandlingService.deleteById(dataHandling.getId());
		if (d) {
			System.out.println("dataHandling 删除成功>>>" + dataHandling.getId());
		}
	}

	@Override
	public List<Map<String,Object>> getTableName(String url, String user, String password,
			String coding, String type) {
		List<Map<String,Object>> tableName=new ArrayList<Map<String,Object>>();
		if(!type.equals("mongodb")){
			Connection conn = getConnection(url, user, password, coding, type);
			ResultSet rs = null;
			if(conn!=null){
				if(type.equals("mysql")){
					rs=JdbcUtils.executeSql(conn," SELECT TABLE_NAME NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = (select database())");
				}
				if(type.equals("oracle")){
					rs = JdbcUtils.executeSql(conn,ConstantSql.oracle_tables);
				}
				if(type.equals("access")){
					rs=JdbcUtils.executeSql(conn,ConstantSql.access_tables);
				}
				if(type.equals("sqlserver")){
					rs=JdbcUtils.executeSql(conn,"Select Name as NAME FROM SysObjects Where XType='U' orDER BY Name");
				}
				if(rs!=null){
					tableName = JdbcUtils.getResult(rs);
					
				}
				JdbcUtils.close(rs);
				JdbcUtils.close(conn);
			}
		}else{
			MongoDatabase mongoDatabase = DbConnection.MongoDB.getConnection(url.split(":")[0], user,password, url.split(":")[1]);
			List<String> mongoAllCollections = JdbcUtils.getMongoAllCollections(mongoDatabase);
			for (String string : mongoAllCollections) {
				Map<String, Object>map=new HashMap<String, Object>();
				map.put("NAME", string);
				tableName.add(map);
			}
		}
		return tableName;
	}

	@Override
	public List<Map<String,Object>> getDataBaseName(String url, String user, String password,String coding, String type) {
		List<Map<String, Object>> databaseName=new ArrayList<Map<String, Object>>();
		Connection conn = getConnection(url, user, password, coding, type);
		ResultSet rs = null;
		if(conn!=null){
			if(type.equals("mysql")){
				//rs = JdbcUtils.executeSql(conn, ConstantSql.mysql_databases);
			}
			if(rs != null){
				databaseName = JdbcUtils.getResult(rs);
			}
		}
		return databaseName;
	}

	@Override
	public List<Map<String, Object>> getField(String url, String user, String password,String coding, String type,String tableName) {
		List<Map<String, Object>> tableInfo =null;
		if(type.equals("mongodb")){
			MongoDatabase mongoDatabase = getConnection(url, user, password);
			try {
				tableInfo = JdbcUtils.getMongoTableInfo(mongoDatabase, tableName);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			Connection conn = getConnection(url, user, password, coding, type);
			if(conn!=null){
				try {
					tableInfo = JdbcUtils.getTableInfo(conn, tableName);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return tableInfo;
	}
	
	@Override
	public List<Map<String,String>> getExcelSheetName(String inPath){
		String[]sheetName=null;
		List<Map<String,String>>listMap = new ArrayList<Map<String,String>>();
		Map<String,String> map = null;
		try {
			if(inPath.endsWith(".xls")){//excel2003
				HSSFWorkbook wb = FileUtils.Execl.getHSSFWork(inPath);
				sheetName = FileUtils.Execl.getExcelSheetName(wb);
			}else if(inPath.endsWith(".xlsx")){//excel2007
				XSSFWorkbook wb = FileUtils.Execl.getXSSFWork(inPath);
				sheetName = FileUtils.Execl.getExcelSheetName(wb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(sheetName!=null){
			for (int i = 0; i < sheetName.length; i++) {
				map=new HashMap<String,String>();
				map.put("sheetName", sheetName[i]);
				listMap.add(map);
			}
		}
		return listMap;
	}
	@Override
	public List<Map<String, String>> getTxtField(String inPath,String type,String rowRegex, String fieldRegex) {
		List<Map<String, String>> fieldName = new ArrayList<Map<String,String>>();
		try {
			if(type.equals("0")){//一行一条数据库记录 title:xxx   summer：xxx
				fieldName = FileUtils.Txt.getFieldName(inPath, rowRegex, fieldRegex);
			}else if(type.equals("1")){//多行一条数据库记录
				fieldName = FileUtils.Txt.getFieldName(inPath, fieldRegex);
			}else{//txt表格方式
				fieldName = TxtUtil.getTxtColumn(inPath, rowRegex);
			}
		} catch (Exception e) {
		}
		return fieldName;
	}

	@Override
	public List<Map<String, String>> getExcelField(String inPath,String sheetName){
		String[] columnName = null;
		List<Map<String,String>>listMap = new ArrayList<Map<String,String>>();
		Map<String,String> map = null;
		try {
			if(inPath.endsWith(".xls")){//excel2003
				HSSFWorkbook wb = FileUtils.Execl.getHSSFWork(inPath);
				columnName = FileUtils.Execl.getColumnName(wb, sheetName);
			}else if(inPath.endsWith(".xlsx")){//excel2007
				XSSFWorkbook wb = FileUtils.Execl.getXSSFWork(inPath);
				columnName = FileUtils.Execl.getColumnName(wb, sheetName);
			}
		} catch (Exception e) {
		}
		if(columnName!=null){
			for (int i = 0; i < columnName.length; i++) {
				map=new HashMap<String,String>();
				map.put("fieldName", columnName[i]);
				listMap.add(map);
			}
		}
		return listMap;
	}

	@Override
	public List<Map<String, String>> getJsonField(String inPath){
		List<Map<String, String>> jsonFieldName=null;
		try {
			jsonFieldName = FileUtils.Txt.getJsonFieldName(inPath);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return jsonFieldName;
	}

	@Override
	public List<Map<String, String>> getXmlField(String inPath,String elementPath) {
		List<Map<String, String>> fieldName= new ArrayList<Map<String,String>>();
		try {
			SAXParserFactory factory = SAXParserFactory.newInstance();
			SAXParser parser = factory.newSAXParser();
			ReadXml xml =  new ReadXml(elementPath,0,1, true);
			parser.parse(inPath, xml);
			List<Map<String, Object>> list2 = xml.list;
			for (String key : list2.get(0).keySet()) {
				Map<String, String> m = new HashMap<String, String>();
				m.put("fieldName", key);
				fieldName.add(m);
			}
			/*
			Document doc = FileUtils.Xml.getDocument(inPath);
			fieldName = FileUtils.Xml.getFieldName(doc, elementPath);
			*/
		} catch (Exception e) {
			e.printStackTrace();
		}
		return fieldName;
	}

	@Override
	public void importData(DataHandling dataHandling,int pagesize,int count,int pagenum) throws Exception {
		List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();
		DataSource dataSource = new DataSource();
		dataSource=publicService.findDataSource(dataHandling.getDataSourceId());
		String sourceType = dataSource.getSourceType();
		String url = dataSource.getUrl();
		String user = dataSource.getUser();
		String password = dataSource.getPassword();
		String coding = dataSource.getCoding();
		String tableName=dataHandling.getTableName();
		String filePath = dataHandling.getFilePath();
		String sheetName = dataHandling.getSheetName();
		String elementPath=dataHandling.getXpathPath();
		String txtType=dataHandling.getTxtType();
		String fieldFieldSplit = dataHandling.getFieldFieldSplit();
		String fieldSplit = dataHandling.getFieldSplit();
		//String storeTableName = getStoreTableName(dataHandling.getClassesId());
		
		Classes clazz = publicService.findClasses(dataHandling.getClassesId());
		String classesEn=clazz.getNameEn();
		
		
		//Connection zskConnection =null; //getZSKConnection();
		//MongoDatabase zskMongoConnection=null;
		//zskConnection = getZSKConnection();
		 /*是否开启分库分表*/
		HashMap<String, Object> srcQueryParams = new HashMap<>();
		srcQueryParams.put("=,classesEn", classesEn);
		List<ShardingRepositoryCount> srcList = publicService
				.getShardingRepositoryCount(srcQueryParams, 1, 500);
		boolean sharding = false;
		if(srcList.size() > 0){
			sharding = true;
		}
		
		
		Connection conn = null;
		if(sourceType.equals("mysql")){
			conn = getConnection(url, user, password, coding, sourceType);
			String sql = tableName+"  LIMIT "+(pagenum)*pagesize +","+pagesize;
			ResultSet searchSql = JdbcUtils.searchSql(conn, sql);
			result = JdbcUtils.getResult(searchSql);
			List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
			
			//HashMap<String, Object> queryParams = new HashMap<String, Object>();
			//queryParams.put("=,nameEn", classesEn);
			//Integer shardingDataSourceCount = publicService.getShardingDataSourceCount(queryParams );
			
			dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
			
			//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
			//JdbcUtils.batchInsert(zskConnection, result, getMap(dataHandling.getCorrespondingField()), storeTableName,dataHandling.getClassesId());
			//JdbcUtils.close(searchSql);
		}else if(sourceType.equals("oracle")){
			conn = getConnection(url, user, password, coding, sourceType);
			String sql =" (SELECT A.*, ROWNUM RN FROM (SELECT * FROM "+tableName+") A WHERE ROWNUM <= "+(pagenum+1)*pagesize+" ) WHERE RN >= "+(pagenum)*pagesize;
			ResultSet searchSql = JdbcUtils.searchSql(conn, sql);
			result = JdbcUtils.getResult(searchSql);
			List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
			dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
			
			//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
			//JdbcUtils.batchInsert(zskConnection, result, getMap(dataHandling.getCorrespondingField()), storeTableName,dataHandling.getClassesId());
			//JdbcUtils.close(searchSql);
		}else if(sourceType.equals("sqlserver")){
			// sql server数据传输必须要有id字段，id为主键，自增长
			conn = getConnection(url, user, password, coding, sourceType);
			String sql="SELECT w2.n, w1.* FROM "+tableName+" w1, (SELECT TOP "+(pagenum+1)*pagesize+" row_number() OVER (ORDER BY id DESC) n, ID FROM "+tableName+" ) w2 WHERE w1.ID = w2.ID AND w2.n > "+(pagenum)*pagesize+" ORDER BY w2.n ASC";
			ResultSet searchSql = JdbcUtils.executeSql(conn, sql);
			result = JdbcUtils.getResult(searchSql);
			
			List<Map<String, Object>> dynamicDatas = new ArrayList();
			Map<String, Object> dyMap = null;
			List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
			for (int i = 0; i < replaceField.size(); i++) {
				Map<String, Object> map = replaceField.get(i);
				dyMap = new HashMap<String, Object>();
				Iterator iter = map.entrySet().iterator();
				while (iter.hasNext()) {
					Map.Entry entry = (Map.Entry) iter.next();
					String field = entry.getKey().toString();
					Object value = entry.getValue();
					StringBuffer sb=new StringBuffer();
					if(value!=null) {
						//应该这里调用替换的方法
						String s = "";
						char[] charArray = value.toString().toCharArray();
						for (char c : charArray) {
							if (!(c < 0x9 || c > 0x9 && c < 0xA || c > 0xA && c < 0xD || c > 0xD && c  
									< 0x20 || c > 0xD7FF && c < 0xE000 || c > 0xFFFD)){//下面了允许的字符范围
								s += c;
							}
						}
						sb.append(s);
					}
					dyMap.put(field, sb.toString());
				}
				dynamicDatas.add(dyMap);
			}
			dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
			
			//dynamicService.addDynamicData(classesEn, dynamicDatas, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
			//JdbcUtils.batchInsert(zskConnection, result, getMap(dataHandling.getCorrespondingField()), storeTableName,dataHandling.getClassesId());
			//JdbcUtils.close(searchSql);
		}else if(sourceType.equals("access")){//???这个分页还没写
			conn = getConnection(url, user, password, coding, sourceType);
			//int count = JdbcUtils.getCount(conn, tableName);
			String sql="";
			if(pagenum == count/pagesize){
				sql="SELECT * FROM (SELECT TOP "+(count-pagenum*pagesize)+" * FROM (SELECT TOP "+((pagenum+1)*pagesize)+" * FROM "+tableName+" ORDER BY id desc) ORDER BY id) ORDER BY id desc";
			}else{
				sql="SELECT * FROM (SELECT TOP "+pagesize+" * FROM (SELECT TOP "+((pagenum+1)*pagesize)+" * FROM "+tableName+" ORDER BY id desc) ORDER BY id) ORDER BY id desc";
			}
			ResultSet searchSql = JdbcUtils.executeSql(conn, sql);
			result = JdbcUtils.getResult(searchSql);
			List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
			dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
			
			//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
			//JdbcUtils.batchInsert(zskConnection, result, getMap(dataHandling.getCorrespondingField()), storeTableName,dataHandling.getClassesId());
			//JdbcUtils.close(searchSql);
		}else if(sourceType.equals("mongodb")){
			MongoDatabase mongoDatabase = getConnection(url, user, password);
			FindIterable<org.bson.Document> serachMongo = JdbcUtils.serachMongo(mongoDatabase, tableName, null, (pagenum)*pagesize, pagesize);
			List<Map<String,Object>> mongoToListMap = JdbcUtils.getMongoToListMap(serachMongo);
			//JdbcUtils.batchInsert(zskConnection, mongoToListMap, getMap(dataHandling.getCorrespondingField()), storeTableName, dataHandling.getClassesId());
			List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), mongoToListMap);
			dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
			
			//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
		}else{
			boolean bb=false;
			if (sourceType.equals("txt")) {
				if (txtType.equals("0")) {//一行一条数据库记录 
					result = FileUtils.Txt.readTxtFile(filePath,fieldFieldSplit, fieldSplit);
				} else if(txtType.equals("1")){//多行一条数据库记录
					result = FileUtils.Txt.readTxtFile(filePath, fieldSplit);
				}else{//表格方式
					bb=true;
					result = TxtUtil.readTxt(filePath, 1, 100000, fieldFieldSplit);
					int txtCount = 0;
					while(result != null && result.size()>0){
						List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
						dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
						
						//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
						txtCount = txtCount + 1;
						System.out.println("txt插入条数："+txtCount+"---"+replaceField.size());
						result = TxtUtil.readTxt(filePath, txtCount*100000+1, 100000, fieldFieldSplit);
					}
					
				}
			}else if (sourceType.equals("xml")) {
				bb = true;
				//result = FileUtils.Xml.readXml(filePath, elementPath);
				SAXParserFactory factory = SAXParserFactory.newInstance();
				SAXParser parser = factory.newSAXParser();
				long start = System.currentTimeMillis();
				ReadXml xml = new ReadXml(elementPath, 0, 100000, false);
				parser.parse(filePath, xml);
				long end = System.currentTimeMillis();
				List<Map<String, Object>> listMap = xml.list;
				List<Map<String, Object>> replaceField = null;
				int a = 0;
				System.out.println((end - start) + "ms");
				while (listMap != null && listMap.size() > 0) {
					replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), listMap);
					dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
					
					//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
					
					a = a + 1;
					System.out.println(a);
					start = System.currentTimeMillis();
					xml = new ReadXml(elementPath, a * 100000, 100000, false);
					parser.parse(filePath, xml);
					listMap = xml.list;
					
//					replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), listMap);
//					dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
					
					end = System.currentTimeMillis();
					System.out.println((end - start) + "ms");
				}
			} else if (sourceType.equals("json")) {
				result = FileUtils.Txt.readJsonFile(filePath);
			} else if (sourceType.equals("excel")) {
				result = FileUtils.Execl.readExcel(filePath, sheetName);
			}
			if(!bb){
				//JdbcUtils.batchInsert(zskConnection, result, getMap(dataHandling.getCorrespondingField()), storeTableName,dataHandling.getClassesId());
				List<Map<String, Object>> replaceField = JdbcUtils.replaceField(getMap(dataHandling.getCorrespondingField()), result);
				dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"", sharding);
				
				//dynamicService.addDynamicData(classesEn, replaceField, DateUtils.getDateTime(), "1", "0", "1.0", dataHandling.getClassesId()+"", dataSource.getId()+"");
			}
		}
		//JdbcUtils.close(conn);
		//JdbcUtils.close(zskConnection);
	}
	

	@Override
	public void exportData(DataHandling dataHandling,List<Map<String,Object>>list) throws Exception {
		Map<String, Object> map1 =null;
		List<Map<String,Object>>list1=new ArrayList<Map<String,Object>>();
		for (int i = 0; i < list.size(); i++) {
			map1=new HashMap<String, Object>();
			for (String key : list.get(i).keySet()) {
				map1.put(key, list.get(i).get(key)+"");
			}
			list1.add(map1);
		}
		DataSource dataSource = new DataSource();
		dataSource=publicService.findDataSource(dataHandling.getDataSourceId());
		String url= dataSource.getUrl();
		String user=dataSource.getUser();
		String password=dataSource.getPassword();
		String coding=dataSource.getCoding();
		String sourceType=dataSource.getSourceType();
		
		if(sourceType.equals("mongodb")){
			MongoDatabase mongoDatabase = getConnection(url, user, password);
			List<org.bson.Document> listMapToDocuments = JdbcUtils.getListMapToDocuments(/*map, */list1);
			JdbcUtils.insertMongodb(mongoDatabase,dataHandling.getTableName(), listMapToDocuments);
		}else{
			Map<String, String> map = getMap(dataHandling.getCorrespondingField());
			Connection conn = getConnection(url, user, password, coding, sourceType);
			JdbcUtils.batchInsert(conn, list1, map, dataHandling.getTableName(), -1);
			JdbcUtils.close(conn);
		}
	}
	
	/**
	 * 获取数据源总数
	 */
	@Override
	public int getDataSourceCount(DataHandling dataHandling) {
		DataSource dataSource = new DataSource();
		dataSource=publicService.findDataSource(dataHandling.getDataSourceId());
		String sourceType = dataSource.getSourceType();
		String url = dataSource.getUrl();
		String user = dataSource.getUser();
		String password = dataSource.getPassword();
		String coding = dataSource.getCoding();
		String tableName=dataHandling.getTableName();
		Connection conn = null;
		int count=0;
		if(sourceType.equals("mysql")){
			conn = getConnection(url, user, password, coding, sourceType);
			count = JdbcUtils.getCount(conn, tableName);
			
		}else if(sourceType.equals("oracle")){
			conn = getConnection(url, user, password, coding, sourceType);
			count = JdbcUtils.getCount(conn, tableName);
		}else if(sourceType.equals("sqlserver")){
			conn = getConnection(url, user, password, coding, sourceType);
			count = JdbcUtils.getCount(conn, tableName);
		}else if(sourceType.equals("access")){//???这个分页还没写
			conn = getConnection(url, user, password, coding, sourceType);
			count = JdbcUtils.getCount(conn, tableName);
		}else if(sourceType.equals("mongodb")){
			MongoDatabase mongoDatabase = getConnection(url, user, password);
			count = (int)JdbcUtils.getMongodbCount(mongoDatabase, tableName, null);
		}
		JdbcUtils.close(conn);
		return count;
	}
	/**
	 * 获取知识库总数
	 * @throws Exception 
	 */
	@Override
	public int getStoreCount(String tableName) throws Exception {
		Connection conn=getZSKConnection();
		int count=0;
		count = JdbcUtils.getCount(conn, tableName);
		JdbcUtils.close(conn);
		return count;
	}
	/**
	 * 对应关系 转换 为map
	 * @param str
	 * @return
	 */
	public Map<String,String> getMap(String str){
		Map<String, String> map = new HashMap<String, String>();
		JSONObject fromObject = JSONObject.fromObject(str);
		for (Object key : fromObject.keySet()) {
			map.put(key+"", fromObject.get(key)+"");
		}
		return map;
	}
	public String getStoreTableName(int id){
		Classes clazz=new Classes();
		clazz = publicService.findClasses(id);
		String tableName="hymax_"+clazz.getNameEn();
		return tableName;
	}
	
	/**
	 * 组装大数据仓储insert语句
	 * @param sourceList
	 * @param dataHandling
	 * @return
	 */
	public List<String> getbatchSql(List<Map<String,String>> sourceList,DataHandling dataHandling){
		String correspondingField = dataHandling.getCorrespondingField();//对应字段
		List<String> bsql=new ArrayList<String>();
		Map<String, String> map = new HashMap<String, String>();
		JSONObject fromObject = JSONObject.fromObject(correspondingField);
		for (Object key : fromObject.keySet()) {
			map.put(key+"", fromObject.get(key)+"");
		}
		Classes clazz=new Classes();
		clazz = publicService.findClasses(dataHandling.getClassesId());
		String tableName="hymax_"+clazz.getNameEn();
		bsql=JdbcUtils.getbatchSql(tableName, map, sourceList);
		return bsql;
	}
	/**
	 * 获取jdbc链接
	 * @param url
	 * @param user
	 * @param password
	 * @param coding
	 * @param type
	 * @return
	 */
	public Connection getConnection(String url, String user, String password,String coding, String type){
		Connection conn = null;
		try {
			if(type.equals("mysql")){
				conn  = DbConnection.MySQL.getConnection(url, user, password);
			}
			if(type.equals("access")){
				conn  = DbConnection.Access.getConnection(url,coding, user, password);
			}
			if(type.equals("oracle")){
				conn  = DbConnection.Oracle.getConnection(url, user, password);
			}
			if(type.equals("sqlserver")){
				conn  = DbConnection.SQLServer.getConnection(url, user, password);
			}
		} catch (Exception e) {
		}
		return conn;
	}
	
	public MongoDatabase getConnection(String url, String user, String password){
		MongoDatabase mongoDatabase=null;
		String[] split = url.split(":");
		mongoDatabase = DbConnection.MongoDB.getConnection(split[0], user, password,split[1]);
		return mongoDatabase;
	}
	
	/**
	 * 获取主键
	 * @param conn
	 * @param type
	 * @param tableName
	 * @return
	 */
	public String getPriKey(Connection conn,String type,String tableName){
		String sql="";
		if("mysql".equals(type)){
			sql="select COLUMN_NAME as pri from INFORMATION_SCHEMA.COLUMNS where table_name='"+tableName+"' AND COLUMN_KEY='PRI'";
		}//DISTINCT
		ResultSet searchSql = JdbcUtils.executeSql(conn, sql);
		List<Map<String, Object>> result = JdbcUtils.getResult(searchSql);
		JdbcUtils.close(searchSql);
		return result.get(0).get("pri")+"";
	}
	
	public Connection getZSKConnection() throws SQLException{
		//获取知识库链接部分
		Connection conn = null;
		/*
		conn = DriverManager.getConnection(
				XxlConfClient.get("dal.jdbc.url", ""),
				XxlConfClient.get("dal.jdbc.username", ""),
				XxlConfClient.get("dal.jdbc.password", ""));
		*/
		//ResourceBundle resource = ResourceBundle.getBundle("config/database");
		String driver=XxlConfClient.get("dal.jdbc.driver", "");
		String url=XxlConfClient.get("dal.jdbc.url", "");
		String username = XxlConfClient.get("dal.jdbc.username", "");
		String password=XxlConfClient.get("dal.jdbc.password", "");
		
		if(driver.contains("com.mysql.jdbc.Driver")){
			conn = getConnection(url, username, password, "", "mysql");
		}else if(driver.contains("oracle.jdbc.driver.OracleDriver")){
			conn = getConnection(url, username, password, "", "oracle");
		}else if(driver.contains("net.sourceforge.jtds.jdbc.Driver")){
			conn = getConnection(url, username, password, "", "sqlserver");
		}else if(driver.contains("sun.jdbc.odbc.JdbcOdbcDriver")){
			conn = getConnection(url, username, password, "", "access");
		}else{
			conn = getConnection(url, username, password, "", "mongodb");
		}
		return conn;
	}
	
	public MongoDatabase getZSKMongoConnection(){
		String driver=XxlConfClient.get("dal.jdbc.driver", "");
		String url=XxlConfClient.get("dal.jdbc.url", "");
		String username = XxlConfClient.get("dal.jdbc.username", "");
		String password=XxlConfClient.get("dal.jdbc.password", "");
		
		MongoDatabase mongoDatabase=null;
		mongoDatabase=getConnection(url, username, password);
		return mongoDatabase;
	}

	@Override
	public void dataBackup(String classesEn, String allOrZeng, String path) throws Exception {
		// TODO Auto-generated method stub
	}

	@Override
	public Map<String,String> dataRecovery(String classesEn, String path)  throws Exception{
		SAXParserFactory factory = SAXParserFactory.newInstance();
		Map<String,String> head = new HashMap<String, String>();
		Map<String,String> m = new HashMap<String, String>(); 
		
		int count = 0;
		String startId="";
		String endId="";
		
		SAXParser parser = factory.newSAXParser();
		long start = System.currentTimeMillis();
		ReadXml xml = new ReadXml("//entities/entity", 0, 100000, false);
		parser.parse(path, xml);
		long end = System.currentTimeMillis();
		List<Map<String, Object>> listMap = xml.list;
		for (int i = 0; i < listMap.size(); i++) {
			if(i==0){
				for (String key : listMap.get(i).keySet()) {
					head.put(key, key);
				}
			}
		}
		startId = listMap.get(0).get("id").toString();
		m.put("startId", startId);
		
		int a = 0;
		System.out.println((end - start) + "ms");
		while (listMap!=null && listMap.size() > 0) {
			count = count + listMap.size();
			endId = listMap.get(listMap.size()-1).get("id").toString();
			m.put("endId", endId);
			m.put("count", count+"");
			insertBatch(head, listMap, classesEn);
			
			a = a + 1;
			System.out.println(a);
			start = System.currentTimeMillis();
			xml = new ReadXml("//entities/entity", a * 100000, 100000, false);
			parser.parse(path, xml);
			listMap = xml.list;
			//insertBatch(head, listMap, classesEn);
			//endId = listMap.get(listMap.size()-1).get("id").toString();
			//m.put("endId", endId);
			//m.put("count", count+"");
			//count = count + listMap.size();
			end = System.currentTimeMillis();
			System.out.println((end - start) + "ms");
		}
		return m;
	}
	private void insertBatch(Map<String,String>head,List<Map<String,Object>>listMap,String classesEn){
		Connection conn;
		HashMap<String, Object> queryParams = new HashMap<String, Object>();
        queryParams.put("=,classesEn", classesEn);
        Integer shardingRepositoryCountCount = publicService.getShardingRepositoryCountCount(queryParams);
		if(!shardingRepositoryCountCount.equals(0)){//分库分表
			 List<ShardingRepositoryCount> shardingRepositoryCounts = publicService.getShardingRepositoryCount(queryParams, 1, shardingRepositoryCountCount);
			 for (ShardingRepositoryCount src : shardingRepositoryCounts) {
				 //数据源
				 ShardingDataSource shardingDataSourceId = publicService.findShardingDataSource(src.getShardingDataSourceId());
				 queryParams = new HashMap<String, Object>();
				 queryParams.put("=,classesEn", classesEn);
				 queryParams.put("=,shardingDataSourceId", src.getShardingDataSourceId());
				 List<ShardingRepositoryDataSource> shardingRepositoryDataSources = publicService.getShardingRepositoryDataSource(queryParams, 1, 1);
				 Integer shardingThreshold = shardingRepositoryDataSources.get(0).getShardingThreshold();//阈值
				 try {
					 conn = DbConnection.MySQL.getConnection(shardingDataSourceId.getDbUrl(), shardingDataSourceId.getDbUser(), shardingDataSourceId.getDbPassword());
					 int count = JdbcUtils.getCount(conn, src.getTableName());
					 if(count - shardingThreshold < 0){//未存满
						 JdbcUtils.batchInsert(conn, listMap, head, src.getTableName(), -1);
						 break;
					 }
				 } catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}else{
			try {
				conn = getZSKConnection();
				JdbcUtils.batchInsert(conn, listMap, head, "hymax_"+classesEn, -1);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public int applicationUserAddBatch(String filePath, String sheetName, String type,String memberId) throws Exception {
		int index = 0;
		List<Map<String, Object>> readExcel = FileUtils.Execl.readExcel(filePath, sheetName);
		InetAddress addr = InetAddress.getLocalHost();
		String createIpAddress = addr.getHostAddress();
		for (Map<String, Object> map : readExcel) {
			Member member= new Member();
			member.setId((int)(aIdWorker.nextId()));
			member.setAddress(map.get("地址")==null?null:map.get("地址")+"");
			member.setAge((map.get("年龄")==null || "".equals((map.get("年龄")+"").trim()))?null:Integer.parseInt(map.get("年龄")+""));
			member.setAvatarImg(map.get("头像")==null?null:map.get("头像")+"");
			member.setBalance((map.get("余额")==null || "".equals((map.get("余额")+"").trim()))?new BigDecimal(0):new BigDecimal(map.get("余额")+""));
			member.setCertificateNumber(map.get("证件号")==null?null:map.get("证件号")+"");
			if("身份证".equals(map.get("证件类型"))){
				member.setCertificateType(0);
			}else if("军官证".equals(map.get("证件类型"))){
				member.setCertificateType(1);
			}else if("护照".equals(map.get("证件类型"))){
				member.setCertificateType(2);
			}else if("其他".equals(map.get("证件类型"))){
				member.setCertificateType(3);
			}
			member.setCreateIpAddress((map.get("创建ip地址")==null || "".equals((map.get("创建ip地址")+"").trim()))?createIpAddress:map.get("创建ip地址")+"");
			member.setCreateTime((map.get("创建时间")==null || "".equals((map.get("创建时间")+"").trim()))?DateUtils.getDateTime():map.get("创建时间")+"");
			member.setEducation(map.get("学历")==null?null:map.get("学历")+"");
			member.setEmail(map.get("邮箱")==null?null:map.get("邮箱")+"");
			if("男".equals(map.get("性别"))){
				member.setGender(1);
			}else if("女".equals(map.get("性别"))){
				member.setGender(0);
			}else{
				member.setGender(2);
			}
			Map<String, Object> keyMap = CreateSecrteKey.initKey();
		    String keys = CreateSecrteKey.getPrivateKey(keyMap);
			member.setKeys((map.get("密钥")==null || "".equals((map.get("密钥")+"").trim()))?keys:map.get("密钥")+"");
			if("ip自动登录".equals(map.get("登录方式"))){
				member.setLoginMode(1);
			}else{
				member.setLoginMode(0);
			}
			member.setMemberId((memberId==null || "".equals(memberId.trim())?null:Integer.parseInt(memberId)));
			member.setMobilephone(map.get("手机号")==null?null:map.get("手机号")+"");
			member.setNickname(map.get("昵称")==null?null:map.get("昵称")+"");
			member.setPassword(map.get("密码")==null?MD5.convert("123456"):MD5.convert(map.get("密码")+""));
			member.setPayment((map.get("已支付金额")==null || "".equals((map.get("已支付金额")+"").trim()))?new BigDecimal(0):new BigDecimal(map.get("已支付金额")+""));
			member.setProfession(map.get("专业")==null?null:map.get("专业")+"");
			member.setQq(map.get("qq账号")==null?null:map.get("qq账号")+"");
			member.setRealname(map.get("真实姓名")==null?null:map.get("真实姓名")+"");
			member.setResearchField(map.get("研究领域")==null?null:map.get("研究领域")+"");
			member.setSina(map.get("新浪账号")==null?null:map.get("新浪账号")+"");
			if("禁用".equals(map.get("状态"))){
				member.setStatus(1);
			}else{
				member.setStatus(0);
			}
			member.setTelephone(map.get("联系电话")==null?null:map.get("联系电话")+"");
			member.setTitle(map.get("职称")==null?null:map.get("职称")+"");
			member.setType(Integer.parseInt(type));
			member.setUsername(map.get("用户名")==null?null:map.get("用户名")+"");
			member.setValidity(map.get("有效期")==null?null:map.get("有效期")+"");
			member.setWeixin(map.get("微信账号")==null?null:map.get("微信账号")+"");
			member.setWorkunit(map.get("工作单位")==null?null:map.get("工作单位")+"");
		    baseService.saveMember(member);
		    index++;
		}
		return index;
	}
}
